package com.wuad.platformwuad.module.Excel.controller;

import com.wuad.platformwuad.util.JXLColoutTypeUtils;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.format.*;
import jxl.write.*;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.OutputStream;

/**
 * jxl Excel下载模板 支持自定义模板标题等
 *
 * @author ：Wuad
 * @date ：Created in 2021/2/19 8:43
 * @description :
 */
@RestController
@RequestMapping("/excel")
public class ExcelOutputByJXLController {

        /**
         * 下载文件
         *
         * @return
         */
        @RequestMapping({"/download"})
        public void download() {
            ServletRequestAttributes requestAttributes = (ServletRequestAttributes) RequestContextHolder.getRequestAttributes();
            HttpServletResponse response = requestAttributes.getResponse();
            HttpServletRequest request = requestAttributes.getRequest();

            // 文件名
            String filename = "导入客户模板";

            try {

                // 写到服务器上
                String path = request.getSession().getServletContext().getRealPath("") + "/" + filename;

                // 写到服务器上（这种测试过，在本地可以，放到linux服务器就不行）
                //String path =  this.getClass().getClassLoader().getResource("").getPath()+"/"+filename;

                File name = new File(path);
                // 创建写工作簿对象
                WritableWorkbook workbook = Workbook.createWorkbook(name);
                // 工作表
                WritableSheet sheet = workbook.createSheet("导入客户模板", 0);
                // 合并单元格
                sheet.mergeCells(0 , 0 , 5 , 0);
                sheet.setRowView(0,2400);
                // 分别给1,5,6列设置不同的宽度;
//                sheet.setColumnView(0, 15);
//                sheet.setColumnView(4, 60);
//                sheet.setColumnView(5, 35);
                // 给sheet电子版中所有的列设置默认的列的宽度;
                sheet.getSettings().setDefaultColumnWidth(20);
                // 给sheet电子版中所有的行设置默认的高度，高度的单位是1/20个像素点,但设置这个貌似就不能自动换行了
                // sheet.getSettings().setDefaultRowHeight(30 * 20);


                // 给首行设置字体(字体，字体大小，)
                WritableFont font1 = new WritableFont(
                        WritableFont.ARIAL, // 字体
                        14,  // 字号
                        WritableFont.BOLD, // 粗体
                        false, // 斜体
                        UnderlineStyle.NO_UNDERLINE, // 下划线
                        Colour.RED // 字体颜色
                );
                // 给首行设置格式
                WritableCellFormat cellFormat1 = new WritableCellFormat(font1);
                cellFormat1.setBackground(Colour.WHITE);
                cellFormat1.setBorder(Border.ALL, BorderLineStyle.THIN);
                cellFormat1.setAlignment(Alignment.LEFT);
                cellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE);
                cellFormat1.setWrap(true);


                /**
                 * 给标题行设置格式样式
                 */
                // 设置字体;
                WritableFont fontRed = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
                WritableFont fontBlack = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
                // 设置格式
                WritableCellFormat cellFormatRed = new WritableCellFormat(fontRed);
                WritableCellFormat cellFormatBlack = new WritableCellFormat(fontBlack);
                // 设置背景颜色;
                JXLColoutTypeUtils coloutType = new JXLColoutTypeUtils(65, "blue", 42, 148, 150);
                cellFormatRed.setBackground(coloutType);
                // 设置边框;
                cellFormatRed.setBorder(Border.ALL, BorderLineStyle.THIN);
                // 设置文字居中对齐方式;
                cellFormatRed.setAlignment(Alignment.CENTRE);
                // 设置垂直居中;
                cellFormatRed.setVerticalAlignment(VerticalAlignment.CENTRE);
                // 设置自动换行;
                cellFormatRed.setWrap(true);

                // 设置背景颜色;
                cellFormatBlack.setBackground(Colour.BLUE_GREY);
                // 设置边框;
                cellFormatBlack.setBorder(Border.ALL, BorderLineStyle.THIN);
                // 设置文字居中对齐方式;
                cellFormatBlack.setAlignment(Alignment.CENTRE);
                // 设置垂直居中;
                cellFormatBlack.setVerticalAlignment(VerticalAlignment.CENTRE);
                // 设置自动换行;
                cellFormatBlack.setWrap(true);


//                jxl.write.NumberFormat nf = new jxl.write.NumberFormat("0"); //定义数值格式
//                jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);
//
//                Label labelNF = new jxl.write.Number(0, 0, meetvo.getNotsuremeng1(), wcfN);
//                sheet.addCell(labelNF);

                // 单元格
                Label label0 = new Label(0, 0, "填写须知：\n" +
                        "1、表头字段不允许编辑或删除\n" +
                        "2、表头中红色字段为必填字段,黑色字段为选填字段\n" +
                        "3、日期格式：YYYY-MM-DD ；日期时间格式：YYYY-MM-DD hh:mm:ss ；省市区格式：省/市/区\n" +
                        "4、电话号码等联系方式存在多条数据时，单元格内用“,”隔开\n" +
                        "5、表单内容填写完毕后，可使用excel的“数据-数据验证-圈释无效数据”功能，查验无效数据", cellFormat1);
                Label label1 = new Label(0, 1, "ID", cellFormatRed);
                Label label2 = new Label(1, 1, "省", cellFormatBlack);
                Label label3 = new Label(2, 1, "市", cellFormatRed);
                Label label4 = new Label(3, 1, "区", cellFormatBlack);
                Label label5 = new Label(4, 1, "详细地址", cellFormatRed);
                Label label6 = new Label(5, 1, "创建时间", cellFormatBlack);
//                Label label11 = new jxl.write.DateTime(5,1,)
//                WritableCell cell = null;
//                cell = sheet.getWritableCell(1,18);
//                wcf = new WritableCellFormat(DateFormats.FORMAT9);
//                cell.setCellFormat(wcf);

                sheet.addCell(label0);
                sheet.addCell(label1);
                sheet.addCell(label2);
                sheet.addCell(label3);
                sheet.addCell(label4);
                sheet.addCell(label5);
                sheet.addCell(label6);

                // 给第二行设置背景、字体颜色、对齐方式等等;
                WritableFont font2 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
                WritableCellFormat cellFormat2 = new WritableCellFormat(font2);
                // 设置文字居中对齐方式;
                cellFormat2.setAlignment(Alignment.CENTRE);
                // 设置垂直居中;
                cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE);
                cellFormat2.setBackground(Colour.WHITE);
                cellFormat2.setBorder(Border.ALL, BorderLineStyle.THIN);
                cellFormat2.setWrap(true);

                // 记录行数
                int n = 1;

                // 查找所有地址
//                List<Address> addressList = excelService.findAll();
//                if (addressList != null && addressList.size() > 0) {
//
//                    // 遍历
//                    for (Address a : addressList) {
//
//                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//                        String createTime = sdf.format(a.getCreateTime());
//
//                        Label lt0 = new Label(0, n, a.getId() + "", cellFormat2);
//                        Label lt1 = new Label(1, n, a.getProvince(), cellFormat2);
//                        Label lt2 = new Label(2, n, a.getCity(), cellFormat2);
//                        Label lt3 = new Label(3, n, a.getArea(), cellFormat2);
//                        Label lt4 = new Label(4, n, a.getAddress(), cellFormat2);
//                        Label lt5 = new Label(5, n, createTime, cellFormat2);
//
//                        sheet.addCell(lt0);
//                        sheet.addCell(lt1);
//                        sheet.addCell(lt2);
//                        sheet.addCell(lt3);
//                        sheet.addCell(lt4);
//                        sheet.addCell(lt5);
//
//                        n++;
//                    }
//                }

                //开始执行写入操作
                workbook.write();
                //关闭流
                workbook.close();

            } catch (Exception e) {
                e.printStackTrace();
            }
            // 第六步，下载excel
            //TODO 需要搞明白这里的下载是怎么个机制或者实现方式
            OutputStream out = null;
            try {

                // 1.弹出下载框，并处理中文
                /** 如果是从jsp页面传过来的话，就要进行中文处理，在这里action里面产生的直接可以用
                 * String filename = request.getParameter("filename");
                 */
                /**
                 if (request.getMethod().equalsIgnoreCase("GET")) {
                 filename = new String(filename.getBytes("iso8859-1"), "utf-8");
                 }
                 */

                response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//            response.addHeader("Content-Disposition", "attachment;filename=fileName" + ".xlsx");

                response.addHeader("content-disposition", "attachment;filename=" + java.net.URLEncoder.encode(filename, "utf-8") + ".xlsx");

                // 2.下载
                out = response.getOutputStream();
                String path3 = request.getSession().getServletContext().getRealPath("") + "/" + filename;

                // inputStream：读文件，前提是这个文件必须存在，要不就会报错
                InputStream is = new FileInputStream(path3);

                byte[] b = new byte[10240];
                int size = is.read(b);
                while (size > 0) {
                    out.write(b, 0, size);
                    size = is.read(b);
                }
                out.close();
                is.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }


}
